﻿using BaseData.Domain;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System.Reflection;

namespace BaseData.ReadAPI
{
    /// <summary>
    /// Excel通过泛型及反射的导出
    /// </summary>
    public static class ExcelExporter
    {
        /// <summary>
        /// 将泛型列表数据导出到Excel文件。
        /// </summary>
        /// <typeparam name="T">要导出数据的数据类型。</typeparam>
        /// <param name="list">要导出的数据列表。</param>
        /// <param name="sheetName">工作表名称</param>
        /// <returns>包含Excel文件内容的内存流。</returns>
        public static byte[] ExportToExcel<T>(List<T> list, string sheetName="表格") where T : class
        {
            //创建一个Excel工作簿
            IWorkbook objects = new XSSFWorkbook();
            //创建一个工作表
            ISheet sheet = objects.CreateSheet(sheetName);

            //获取泛型类型信息
            Type entityType = typeof(T);
            //获取属性信息
            PropertyInfo[] properties = entityType.GetProperties();
            //获取所有导出的属性（排除带有[IgnoreExport]特性的属性）
            var exportProperties = properties.Where(p => p.GetCustomAttributes(typeof(IgnoreExportAttribute), true).Any()).ToList();
            //为表头添加样式
            ICellStyle headerStyle = objects.CreateCellStyle();
            IFont headerFont = objects.CreateFont();
            headerFont.IsBold = true;//设置字体加粗
            headerStyle.SetFont(headerFont);//设置字体加粗
            headerStyle.Alignment = HorizontalAlignment.Center;//水平居中
            //合并单元格
            var cell1 =sheet.CreateRow(0).CreateCell(0);
            cell1.SetCellValue(sheetName);
            cell1.CellStyle = headerStyle;
            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, properties.Length-1));
            //创建标题行
            IRow headerRow = sheet.CreateRow(1);
            for (int i = 0; i < exportProperties.Count(); i++)
            {
                var property = exportProperties[i];
                //创建标题单元格
                ICell cell = headerRow.CreateCell(i);
                //设置标题单元格内容
                var a = property.GetCustomAttribute<IgnoreExportAttribute>();
                cell.SetCellValue(a.Name);
                cell.CellStyle = headerStyle;//将样式应用到单元格
            }

            //填充数据行
            for(int rowIndex=0;rowIndex<list.Count;rowIndex++)
            {
                IRow dataRow = sheet.CreateRow(rowIndex+2);//创建数据行
                T item = list[rowIndex];//获取当前行数据
                //填充数据列
                for(int cellIndex=0;cellIndex<exportProperties.Count();cellIndex++)
                {
                    PropertyInfo prop = exportProperties[cellIndex];//获取当前列属性
                    object value = prop.GetValue(item,null)??DBNull.Value;//获取当前列属性的值
                    ICell cell = dataRow.CreateCell(cellIndex);
                    SetCellFormat(cell,value);
                    var style = objects.CreateCellStyle();
                    style.Alignment = HorizontalAlignment.Center;//设置单元格水平居中
                    
                }
            }
            //自动调整列宽
            for(int i=0;i<properties.Length;i++)
            {
                sheet.AutoSizeColumn(i);
            }

            //保存文件
            using (MemoryStream  fileStream = new MemoryStream())
            {
                objects.Write(fileStream);
                return fileStream.ToArray();
            }
        }
        /// <summary>
        /// 根据值的类型和指定格式设置单元格值。
        /// </summary>
        private static void SetCellFormat(ICell cell,object value)
        {
            if(value == null)
            {
                cell.SetCellType(CellType.Blank);//设置单元格类型为空
                return;
            }
            if(value is DBNull)
            {
                cell.SetCellValue(string.Empty);
            }
            else if(value is DateTime)
            {
                cell.SetCellValue((DateTime)value);//设置单元格值为日期
                ICellStyle style = cell.Sheet.Workbook.CreateCellStyle();//创建单元格样式
                style.DataFormat = cell.Sheet.Workbook.CreateDataFormat().GetFormat("yyyy-MM-dd HH:mm:ss");
                cell.CellStyle = style;
            }
            else if(value is bool)
            {
                cell.SetCellValue((bool)value);
            }
            else if (value is byte[])
            {
                cell.SetCellValue(Convert.ToBase64String((byte[])value));
            }
            else if (value is double)
            {
                cell.SetCellValue((double)value);
            }
            else
            {
                cell.SetCellValue(value.ToString());//默认对于其他类型，都按字符串处理
            }
        }
    }
}
